<?php

/**
 * xml格式的Excel导出
 * Author: FoxxxZhu
 * Date: 2016/11/24
 * Time: 17:41
 * @link https://msdn.microsoft.com/en-us/library/aa140066(v=office.10).aspx
 * @todo 导出图片功能 待添加
 */
class TinyExcel
{

    public $uuid = "C2F41010-65B3-11d1-A29F-00AA00C14882";
    public $author = "FoxxxZhu";
    public $lastAuthor = "FoxxxZhu";
    public $version = "14.00";

    public $created = "";
    public $updated = "";

    /**
     * 列数
     * @var int
     */
    public $columnNumber = 14;

    /**
     * 行数
     * @var int
     */
    public $rowNumber = 3000;

    /**
     * 指定列的样式
     * @var array
     */
    public $columnStyle = [];

//    protected $beforeAll = [0xFF,0xFE];//0xFF,0xFE

//    protected $xmlHeader = array();
//
//    protected $documentProperties = array();
//
//    protected $officeDocumentSettings = array();
//
//    protected $excelWorkbook = array();

    protected $styles = array();

//    protected $worksheet = array();


//    /**
//     * 加入的字符  使xls文件能默认用Excel打开
//     * @return string
//     */
//    public function getTop()
//    {
//        $str = '';
//        foreach($this->beforeAll as $num)
//        {
//            $str .= chr($num);
//        }
//        return $str;
//    }

    public function __construct($columns, $rows, $styles = [])
    {
        $this->columnNumber = $columns;
        $this->rowNumber = $rows;
        $this->styles = $styles;
        $nowDate = date("Y-m-d") . "T" . date("H:i:s") . "Z";
        $this->created = $nowDate;
        $this->updated = $nowDate;
    }

    /**
     * 获取头部信息
     */
    public function getHeaderContent()
    {
        return $this->getHeader() . $this->getXMLConfig() . $this->getStyleContent() . $this->getTableStartContent() . $this->getColumnContent();
    }

    /**
     * 获取尾部信息
     */
    public function getFooterContent()
    {
        return $this->getTableEndContent() . $this->getWorksheetOptionsContent() . $this->getEndContent();
    }

    /**
     * 获取格式化后的行数据
     * @param $row
     * @return string
     */
    public function getRowData($row)
    {
        $maxDeep = self::getRowMaxDeep($row);
        $mainNode = ['tag' => 'Row', 'children' => []];
        $mergeDownNodes = [];
        $mergeDownDatas = [];
        $columnNum = count($row);
        foreach ($row as $column => $value) {
            if ($maxDeep <= 1)//单行模式
            {
                $mainNode['children'][] = $this->row2NodeArray($value, $column);
            } else //多行单元格模式
            {
                $mainNode = [];
                if (!is_array($value)) {
                    $value = [$value];
                }
                foreach ($value as $key => $val) {
                    $mergeDownDatas[$key][$column] = $val;
                }
            }
        }

        $content = self::formatNode($mainNode);

        foreach ($mergeDownDatas as $line => $data) {
            $tmp_line = $line + 1;
            $tmpNode = ['tag' => 'Row', 'children' => []];
            for ($column = 0; $column < $columnNum; $column++) {
                if (isset($mergeDownDatas[$line][$column])) {
                    if (!isset($mergeDownDatas[$tmp_line][$column])) {
                        $mergeDownNum = $maxDeep - $tmp_line;
                    } else {
                        $mergeDownNum = 0;
                    }
                    $config['Cell'] = [];
                    $config['Cell']['ss:Index'] = $column + 1;
                    if ($mergeDownNum > 0) {
                        $config['Cell']['ss:MergeDown'] = $mergeDownNum;
                    }
                    $tmpNode['children'][] = $this->row2NodeArray($mergeDownDatas[$line][$column], $column, $config);
                }
            }
            $mergeDownNodes[] = $tmpNode;
        }
        if (count($mergeDownNodes) > 0) {
            foreach ($mergeDownNodes as $node) {
                $content .= self::formatNode($node);
            }
        }
        return $content;
    }

    /**
     * 获取格式化后的行数据(多行)
     * @param $rows
     * @return string
     */
    public function getRowsData($rows)
    {
        $content = '';
        foreach ($rows as $row) {
            $content .= $this->getRowData($row);
        }
        return $content;
    }

    /**
     * 设置列样式 index 从1开始
     * @param array $params
     * @return $this
     */
    public function setColumnsWidth($params = [])
    {
        foreach ($params as $index => $width) {
            $attrs = ['ss:StyleID' => "table", 'ss:AutoFitWidth' => "0", 'ss:Width' => $width];
            if (!isset($this->columnStyle[$index])) {
                $this->columnStyle[$index] = $attrs;
            } else {
                $this->columnStyle[$index] = array_merge($this->columnStyle[$index], $attrs);
            }
        }
        return $this;
    }

    /**
     * 设置列样式
     * @param $index
     * @param array $params
     * @return $this
     */
    public function setColumnStyle($index, $params = [])
    {
        if (!isset($this->columnStyle[$index])) {
            $this->columnStyle[$index] = $params;
        } else {
            $this->columnStyle[$index] = array_merge($this->columnStyle[$index], $params);
        }
        return $this;
    }

    public function addStyle($id, $styles)
    {
        $this->styles[] = ['tag' => 'Style', 'attributes' => ['ss:ID' => $id], 'children' => $styles];
        return $this;
    }

    public function getHeader()
    {
        return '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:dt="uuid:' . $this->uuid . '" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40">';
    }

    public function getXMLConfig()
    {
        return ' <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">  <Title>WSH Spreadsheet</Title>  <Author>' . $this->author . '</Author>  <LastAuthor>' . $this->author . '</LastAuthor>  <Created>' . $this->created . '</Created>  <LastSaved>' . $this->updated . '</LastSaved>  <Version>' . $this->version . '</Version> </DocumentProperties> <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">  <AllowPNG/> </OfficeDocumentSettings> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">  <WindowHeight>5490</WindowHeight>  <WindowWidth>9660</WindowWidth>  <WindowTopX>0</WindowTopX>  <WindowTopY>0</WindowTopY>  <ProtectStructure>False</ProtectStructure>  <ProtectWindows>False</ProtectWindows> </ExcelWorkbook>';
    }

    public function getStyleContent()
    {
        $default = ' <Styles>  <Style ss:ID="Default" ss:Name="Normal">   <Alignment ss:Vertical="Bottom"/>   <Borders/>   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#000000"/>   <NumberFormat/>  </Style>  <Style ss:ID="table">   <Interior/>   <Protection/>  </Style> <Style ss:ID="Red">   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#FF0000"/>   <Interior/>   <Protection/>  </Style>  <Style ss:ID="Purple">   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#0070C0"/>   <Interior/>   <Protection/>  </Style>  <Style ss:ID="Green">   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#33FF33"/>   <Interior/>   <Protection/>  </Style>  <Style ss:ID="Grey">   <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#A6A6A6"/>   <Interior/>   <Protection/>  </Style>';
        $end = '</Styles>';
        $custom = '';
        foreach ($this->styles as $style) {
            $custom .= self::formatNode($style);
        }
        return $default . $custom . $end;
    }

    public function getTableStartContent()
    {
        return '<Worksheet ss:Name="Sheet1">  <Table ss:ExpandedColumnCount="' . $this->columnNumber . '" ss:ExpandedRowCount="' . $this->rowNumber . '" x:FullColumns="1"   x:FullRows="1" ss:StyleID="table" ss:DefaultRowHeight="15">';
    }

    public function getColumnContent()
    {
        $columnContent = '';
        if (count($this->columnStyle) < 1) {
            return $columnContent;
        }
        ksort($this->columnStyle);
        foreach ($this->columnStyle as $index => $attributes) {
            $attributes['ss:Index'] = $index;
            $attributeStr = $this->formatAttributes($attributes);
            $columnContent .= "<Column{$attributeStr} />";
        }
        return $columnContent;
    }

    public static function formatNode($node)
    {
        if (!isset($node['tag'])) {
            return '';
        }
        $tag = $node['tag'];
        $attributesStr = isset($node['attributes']) ? self::formatAttributes($node['attributes']) : "";
        $text = isset($node['text']) ? $node['text'] : '';
        $childrenStr = "";
        if (isset($node['children']) && count($node['children']) > 0) {
            foreach ($node['children'] as $child) {
                $childrenStr .= self::formatNode($child);
            }
        }
        return "<{$tag}{$attributesStr}>{$text}{$childrenStr}</{$tag}>";
    }

    /**
     * @param $value
     * @param $column
     * @param $extConfig
     * @return array
     */
    public function row2NodeArray($value, $column, $extConfig = [])
    {
        $dataType = 'String';
        if (is_int($value) || is_float($value)) {
            $dataType = 'Number';
        }
        $dataAttributes['ss:Type'] = $dataType;
//        if (isset($this->columnStyle[$column])) {
//            foreach ($this->columnStyle[$column] as $attr => $attrValue) {
//                $dataAttributes[$attr] = $attrValue;
//            }
//        }
        $cellAttributes = [];
        if (isset($extConfig['Cell'])) {
            foreach ($extConfig['Cell'] as $attr => $attrValue) {
                $cellAttributes[$attr] = $attrValue;
            }
        }
        if (isset($extConfig['Data'])) {
            foreach ($extConfig['Data'] as $attr => $attrValue) {
                $dataAttributes[$attr] = $attrValue;
            }
        }

        return ['tag' => 'Cell',
            'attributes' => $cellAttributes,
            'children' => [
                ['tag' => 'Data', 'attributes' => $dataAttributes, 'text' => $value],
            ]
        ];

    }


    public static function formatAttributes($attributes)
    {
        $res = [];
        foreach ($attributes as $attr => $value) {
            $res[] = "{$attr}=\"{$value}\"";
        }
        if (count($res) > 0) {
            return " " . implode(" ", $res);
        }
        return "";
    }

    public function getTableEndContent()
    {
        return '</Table>';
    }

    public function getWorksheetOptionsContent()
    {
        return '  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">   <PageSetup>    <Header x:Margin="0.3"/>    <Footer x:Margin="0.3"/>    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>   </PageSetup>   <Print>    <ValidPrinterInfo/>    <HorizontalResolution>600</HorizontalResolution>    <VerticalResolution>600</VerticalResolution>   </Print>   <Selected/>   <ProtectObjects>False</ProtectObjects>   <ProtectScenarios>False</ProtectScenarios>   <AllowFormatCells/>   <AllowSizeCols/>   <AllowSizeRows/>   <AllowInsertCols/>   <AllowInsertRows/>   <AllowInsertHyperlinks/>   <AllowDeleteCols/>   <AllowDeleteRows/>   <AllowSort/>   <AllowFilter/>   <AllowUsePivotTables/>  </WorksheetOptions>';
    }

    public function getEndContent()
    {
        return ' </Worksheet></Workbook>';
    }

    /**
     * 获取合并单元格的最大深度
     * @param $row
     * @return int|mixed
     */
    public static function getRowMaxDeep($row)
    {
        $max = 1;
        foreach ($row as $val) {
            if (is_array($val)) {
                $max = max($max, count($val));
            }
        }
        return $max;
    }
}

//
////demo
//
////simple
//$testData = [
//    ["name1", "中文", "发生的接口的", 1236, 85456, "2016-11-28 12:12:12", "2016-11-28 12:12:12", "单行"],
//    ["name1", "中文1", "发生的接口的1", 1236, 85456, "2016-11-28 12:12:12", "2016-11-28 12:12:12", "单行"],
//    ["name1", "中文2", "发生的接口的2", 1236, 85456, "2016-11-28 12:12:12", "2016-11-28 12:12:12", "单行"],
//    ["name1", "中文3", "发生的接口的3", 1236, 85456, "2016-11-28 12:12:12", "2016-11-28 12:12:12",[ "多行合并单元格测试",   "多行合并单元格测试",  "多行合并单元格测试",],],
//    ["name1", "中文3", ["多个多行测试","多个多行测试","多个多行测试","多个多行测试"], 1236, 85456, "2016-11-28 12:12:12", "2016-11-28 12:12:12", ["多行合并单元格测试", "多行合并单元格测试",  "多行合并单元格测试", "多行合并单元格测试","多行合并单元格测试", "多行合并单元格测试",  "多行合并单元格测试", "多行合并单元格测试" ]],
//];
//$excel = new TinyExcel(8,10000);
////设置列宽度
//$excel->setColumnsWidth([3=>105.5,6=>100,7=>100,8=>120]);
////设置列颜色 类中预设了 Red(大红) Purple(基佬紫) Gray(灰色)
//$excel->setColumnStyle(1,["ss:StyleID"=>"Red"]);
//
////自定义样式 字体 大小 颜色
//$excel->addStyle("Custom",[
//    ['tag'=>'Font','attributes'=>['ss:FontName'=>'Calibri','ss:Size'=>21,'ss:Color'=>'#0033FF']],
//]);
////使用自定义样式
//$excel->setColumnStyle(1,["ss:StyleID"=>"Custom"]);
//
//
//echo $excel->getHeaderContent();
//echo $excel->getRowsData($testData);
//for($i=0;$i < 100;$i++)
//{
//    echo $excel->getRowsData($testData);
//}
//
//echo $excel->getFooterContent();
//
//
//
